<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class gettables extends CI_Controller {
	/*
	This controller is used for geting data from database.
	INPUT JSON - table name, dimensions, measures
	
	OUTPUT table
	*/
	public function index()
	{
		$this->load->database();
		
		$a = file_get_contents('php://input');
		$data = json_decode($a);
		
		$tableData = array();
		
		// Get Table name
		if (isset($data->table))
		{
			$table = mysql_real_escape_string($data->table);
			
			$query = $this->db->query("select COLUMN_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '" . mysql_escape_string($table) . "' AND NOT CONSTRAINT_NAME = 'PRIMARY'");
			
			foreach( $query->result() as $row)
			{
				$tableData[$row->COLUMN_NAME] = array("table" => $row->REFERENCED_TABLE_NAME,  "column" => $row->REFERENCED_COLUMN_NAME);
				
				//array_push($tableData, array($row->COLUMN_NAME => array($row->REFERENCED_TABLE_NAME, $row->REFERENCED_COLUMN_NAME)));
			}
			
		}
		else
		{
			return;
		}
		
	}
	
	public function getfacttables()
	{
		$this->load->database();
		$databaseName = $this->db->database;
		
		$query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" . $databaseName . "' AND TABLE_NAME LIKE 'f%'";
		
		$query = $this->db->query($query);
		
		$tables->facttables = Array();
		
		foreach( $query->result() as $row )
		{
			array_push($tables->facttables, $row->TABLE_NAME);
		}
		echo json_encode($tables);
		//var_dump($tables);
		//$this->config->load('database');
		//echo $this->config->db->default->database;
		//$query = $this->db->query("SELECT 
	}
	
	public function gettableinfo ($facttable = "")
	{
		//echo $facttable;
		$this->load->database();
		
		$dimension = Array();
		
		$query = "SELECT COLUMN_NAME, REFERENCED_TABLE_NAME AS 'REFTABLE' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '" . mysql_real_escape_string($facttable) . "' AND NOT CONSTRAINT_NAME='PRIMARY'"; 
		
		$query = $this->db->query($query);
		
		foreach ( $query->result() as $row)
		{
			
			$query2 = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . $row->REFTABLE . "' AND NOT COLUMN_KEY = 'PRI'";
			//echo $query2;
			$query2 = $this->db->query($query2);
			
			$dimensionAttributes = array();
			
			foreach ( $query2->result() as $row2)
			 {
				//var_dump($row2);
				array_push($dimensionAttributes, $row2->COLUMN_NAME);
			 }
			 $toPush = null;
			 $toPush->dimensionattributes = $dimensionAttributes;
			 $toPush->dimensionsqlname = $row->COLUMN_NAME;
			 
			array_push($dimension, $toPush);
			//var_dump($row);
		}
		
		//var_dump($dimension);
		
		
		$measures = Array();
		
		$query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . mysql_real_escape_string($facttable) . "' AND COLUMN_KEY = ''";
		
		$query = $this->db->query($query);
		
		foreach ( $query->result() as $row)
		{
			array_push($measures, $row->COLUMN_NAME);
		}
		
		$result->dimensions = $dimension;
		$result->measures = $measures;
		
		echo json_encode($result);
		//var_dump($measures);
	}
}

/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */
?>